Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
Imports System.IO
Imports System.Data

Partial Class AdobePopup3
  Inherits System.Web.UI.Page

  Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    Dim selectSTR, fromSTR, whereSTR, filterSTR As String
    Dim ownerFLAG, insuredFLAG As Boolean

    selectSTR = "SELECT DISTINCT provider_mstr.provider_desc, policy_trans.policy_no, vw_policy_adv_dtl.advisor_name, " & _
      "policy_client_dtl.policy_client_last_name + N', ' + policy_client_dtl.policy_client_first_name + " & _
      "N' ' + policy_client_dtl.policy_client_middle_name AS Name"

    fromSTR = "FROM provider_mstr, policy_trans, vw_policy_adv_dtl, policy_client_dtl"

    whereSTR = "WHERE (provider_mstr.provider_cd = policy_trans.provider_cd) AND " & _
      "(policy_trans.policy_seq_cd = vw_policy_adv_dtl.policy_seq_cd) AND " & _
      "(policy_trans.policy_seq_cd = policy_client_dtl.policy_seq_cd) "

    filterSTR = ""

    ownerFLAG = False : insuredFLAG = False

    If Session("DM_AgentCode") <> "" Then
      whereSTR = whereSTR & "AND (vw_policy_adv_dtl.advisor_cd LIKE '%" & Session("DM_AgentCode") & "%') "
      filterSTR = filterSTR & "Agent Code: " & Session("DM_AgentCode") & vbCrLf
    End If

    If Session("DM_SumInsured") <> "" Then
            whereSTR = whereSTR & "AND (policy_trans.insured_amt = " & Session("DM_SumInsured") & ") "
      filterSTR = filterSTR & "Sum Insured: " & Session("DM_SumInsured") & vbCrLf
    End If

    If Session("DM_Currecy_of_Policy") <> "" Then
      whereSTR = whereSTR & "AND (policy_trans.currency_cd = Curr_Lookup.Lookup_seq) AND " & _
        "(Curr_Lookup.Lookup_cd = N'CURRENCY') AND (Curr_Lookup.Lookup_desc LIKE '%" & _
        Session("DM_Currecy_of_Policy") & "%') "
      fromSTR = fromSTR & ", lookup_mstr AS Curr_Lookup"
      filterSTR = filterSTR & "Currency of Policy: " & Session("DM_Currecy_of_Policy") & vbCrLf
    End If

    If Session("DM_Frequency_of_Payment") <> "" Then
            whereSTR = whereSTR & "AND (policy_trans.frequency_cd = frequency_mstr.frequency_cd) AND " & _
        "(frequency_mstr.frequency_desc LIKE '%" & _
        Session("DM_Frequency_of_Payment") & "%') "
            fromSTR = fromSTR & ", frequency_mstr"
      filterSTR = filterSTR & "Frequency of Payment: " & Session("DM_Frequency_of_Payment") & vbCrLf
    End If

    If Session("DM_Policy_Issue_Date") <> "" Then
      whereSTR = whereSTR & "AND (policy_trans.policy_issue_dt = CONVERT(DATETIME, '" & _
        Session("DM_Policy_Issue_Date") & "', 102)) "
      filterSTR = filterSTR & "Policy Issue Date: " & Session("DM_Policy_Issue_Date") & vbCrLf
    End If

    If Session("DM_Maturity_Term") <> "" Then
      whereSTR = whereSTR & "AND (policy_trans.vanish_yrs = " & Session("DM_Maturity_Term") & ") "
      filterSTR = filterSTR & "Maturity Term: " & Session("DM_Maturity_Term") & vbCrLf
    End If

    If Session("DM_First_Premium_Due_Date") <> "" Then
      whereSTR = whereSTR & "AND (policy_trans.contribution_due_dt = CONVERT(DATETIME, '" & _
        Session("DM_First_Premium_Due_Date") & "', 102)) "
      filterSTR = filterSTR & "First Premium Due Date: " & Session("DM_First_Premium_Due_Date") & vbCrLf
    End If

    If Session("DM_Final_Premium_Due_Date") <> "" Then
     whereSTR = whereSTR & "AND (policy_trans.contribution_due_dt = DATEADD(YEAR, -policy_trans.vanish_yrs, CONVERT(DATETIME, '" & _
       Session("DM_Final_Premium_Due_Date") & "', 102))) "
      filterSTR = filterSTR & "Final Premium Due Date: " & Session("DM_Final_Premium_Due_Date") & vbCrLf
    End If

    If Session("DM_Payment_Status") <> "" Then
      whereSTR = whereSTR & "AND (policy_trans.policy_seq_cd = payment_dtl.policy_seq_cd) AND " & _
        "(payment_dtl.payment_status = Pay_St_Lookup.Lookup_seq) AND " & _
        "(Pay_St_Lookup.Lookup_cd = N'PAYMENT_STATUS') AND (Pay_St_Lookup.Lookup_desc LIKE '" & _
        Session("DM_Payment_Status") & "%') "
      fromSTR = fromSTR & ", lookup_mstr AS Pay_St_Lookup, payment_dtl"    
      filterSTR = filterSTR & "Payment Status: " & Session("DM_Payment_Status") & vbCrLf
    End If

    If Session("DM_Policy_Status") <> "" Then
      whereSTR = whereSTR & "AND (policy_trans.policy_st = Pol_St_Lookup.Lookup_seq) AND " & _
        "(Pol_St_Lookup.Lookup_cd = N'POLICY_STATUS') AND (Pol_St_Lookup.Lookup_desc LIKE '" & _
        Session("DM_Policy_Status") & "%') "
      fromSTR = fromSTR & ", lookup_mstr AS Pol_St_Lookup"
      filterSTR = filterSTR & "Policy Status: " & Session("DM_Policy_Status") & vbCrLf
    End If

    If Session("DM_Process_Status") <> "" Then
      whereSTR = whereSTR & "AND (policy_trans.process_st LIKE '%" & _
        Session("DM_Process_Status") & "%') "
      filterSTR = filterSTR & "Process Status: " & Session("DM_Process_Status") & vbCrLf
    End If

    If Session("DM_Owner_Name") <> "" Then
      whereSTR = whereSTR & "AND (Owner.policy_client_last_name LIKE '%" & Session("DM_Owner_Name") & "%' OR " & _
        "Owner.policy_client_first_name LIKE '%" & Session("DM_Owner_Name") & "%' OR " & _
        "Owner.policy_client_middle_name LIKE '%" & Session("DM_Owner_Name") & "%') "
      ownerFLAG = True
      filterSTR = filterSTR & "Owner Name: " & Session("DM_Owner_Name") & vbCrLf
    End If

    If Session("DM_Owner_Birth_Date") <> "" Then
      whereSTR = whereSTR & "AND (Owner.Birthday = CONVERT(DATETIME, '" & _
        Session("DM_Owner_Birth_Date") & "', 102)) "
      ownerFLAG = True
      filterSTR = filterSTR & "Owner Birth Date: " & Session("DM_Owner_Birth_Date") & vbCrLf
    End If

    If Session("DM_Owner_Occupation") <> "" Then
      whereSTR = whereSTR & "AND (Owner.occupation LIKE '%" & Session("DM_Owner_Occupation") & "%') "
      ownerFLAG = True
      filterSTR = filterSTR & "Owner Occupation: " & Session("DM_Owner_Occupation") & vbCrLf
    End If

    If Session("DM_Owner_Employment_Income") <> "" Then
      whereSTR = whereSTR & "AND (Owner.emp_income = " & Session("DM_Owner_Employment_Income") & ") "
      ownerFLAG = True
      filterSTR = filterSTR & "Owner Employment Income: " & Session("DM_Owner_Employment_Income") & vbCrLf
    End If

    If Session("DM_Owner_Net_Worth") <> "" Then
      whereSTR = whereSTR & "AND (Owner.net_worth = " & Session("DM_Owner_Net_Worth") & ") "
      ownerFLAG = True
      filterSTR = filterSTR & "Owner Net Worth: " & Session("DM_Owner_Net_Worth") & vbCrLf
    End If

    If Session("DM_Owner_Sex") <> "" Then
      whereSTR = whereSTR & "AND (Owner.gender = Owner_G_Lookup.Lookup_seq) AND " & _
        "(Owner_G_Lookup.Lookup_cd = N'GENDER') AND (Owner_G_Lookup.Lookup_desc LIKE '%" & _
        Session("DM_Owner_Sex") & "%') "
      fromSTR = fromSTR & ", lookup_mstr AS Owner_G_Lookup"
      ownerFLAG = True
      filterSTR = filterSTR & "Owner Gender: " & Session("DM_Owner_Sex") & vbCrLf
    End If

    If Session("DM_Owner_Civil_Status") <> "" Then
      whereSTR = whereSTR & "AND (Owner.marital_st = Owner_C_Lookup.Lookup_seq) AND " & _
        "(Owner_C_Lookup.Lookup_cd = N'MARITAL_STATUS') AND (Owner_C_Lookup.Lookup_desc LIKE '%" & _
        Session("DM_Owner_Civil_Status") & "%') "
      fromSTR = fromSTR & ", lookup_mstr AS Owner_C_Lookup"
      ownerFLAG = True
      filterSTR = filterSTR & "Owner Civil Status: " & Session("DM_Owner_Civil_Status") & vbCrLf
    End If

    If Session("DM_Insured_Name") <> "" Then
      whereSTR = whereSTR & "AND (Insured.policy_client_last_name LIKE '%" & Session("DM_Insured_Name") & "%' OR " & _
        "Insured.policy_client_first_name LIKE '%" & Session("DM_Insured_Name") & "%' OR " & _
        "Insured.policy_client_middle_name LIKE '%" & Session("DM_Insured_Name") & "%') "
      insuredFLAG = True
      filterSTR = filterSTR & "Insured Name: " & Session("DM_Insured_Name") & vbCrLf
    End If

    If Session("DM_Insured_Birth_Date") <> "" Then
      whereSTR = whereSTR & "AND (Insured.Birthday = CONVERT(DATETIME, '" & _
        Session("DM_Insured_Birth_Date") & "', 102)) "
      insuredFLAG = True
      filterSTR = filterSTR & "Insured Birth Date: " & Session("DM_Insured_Birth_Date") & vbCrLf
    End If

    If Session("DM_Insured_Occupation") <> "" Then
      whereSTR = whereSTR & "AND (Insured.occupation LIKE '%" & Session("DM_Insured_Occupation") & "%') "
      insuredFLAG = True
      filterSTR = filterSTR & "Insured Occupation: " & Session("DM_Insured_Occupation") & vbCrLf
    End If

    If Session("DM_Insured_Sex") <> "" Then
      whereSTR = whereSTR & "AND (Insured.gender = Insured_G_Lookup.Lookup_seq) AND " & _
        "(Insured_G_Lookup.Lookup_cd = N'GENDER') AND (Insured_G_Lookup.Lookup_desc LIKE '%" & _
        Session("DM_Insured_Sex") & "%') "
      fromSTR = fromSTR & ", lookup_mstr AS Insured_G_Lookup"
      insuredFLAG = True
      filterSTR = filterSTR & "Insured Gender: " & Session("DM_Insured_Sex") & vbCrLf
    End If

    If Session("DM_Insured_Civil_Status") <> "" Then
      whereSTR = whereSTR & "AND (Insured.marital_st = Insured_C_Lookup.Lookup_seq) AND " & _
        "(Insured_C_Lookup.Lookup_cd = N'MARITAL_STATUS') AND (Insured_C_Lookup.Lookup_desc LIKE '%" & _
        Session("DM_Insured_Civil_Status") & "%') "
      fromSTR = fromSTR & ", lookup_mstr AS Insured_C_Lookup"
      insuredFLAG = True
      filterSTR = filterSTR & "Insured Civil Status: " & Session("DM_Insured_Civil_Status") & vbCrLf
    End If

    If ownerFLAG = True Then
      whereSTR = whereSTR & "AND (policy_trans.policy_seq_cd = Owner.policy_seq_cd) "
      fromSTR = fromSTR & ", (SELECT * FROM policy_client_dtl WHERE policy_client_dtl.owner_tag = 1) AS Owner"
    End If

    If insuredFLAG = True Then
      whereSTR = whereSTR & "AND (policy_trans.policy_seq_cd = Insured.policy_seq_cd) "
      fromSTR = fromSTR & ", (SELECT * FROM policy_client_dtl WHERE policy_client_dtl.insured_tag = 1) AS Insured"
    End If


    'Dim sDT As New Data.DataTable

    Dim sConn As New Data.SqlClient.SqlConnection
    Dim sCom As New Data.SqlClient.SqlCommand
    Dim sDApt As New Data.SqlClient.SqlDataAdapter(sCom)
    Dim sDS As New Data.DataSet

    sCom.CommandType = Data.CommandType.Text
    sCom.CommandText = selectSTR & " " & fromSTR & " " & whereSTR & "ORDER BY Name"

    Try
      'sConn.ConnectionString = "Data Source=HEAVEN;Initial Catalog=CIMS;Integrated Security=True"

      sConn.ConnectionString = System.Configuration.ConfigurationManager.AppSettings("CnnStr")
      sCom.Connection = sConn
      sConn.Open()
      sDApt.Fill(sDS)

      sDS.Tables(0).TableName = "data_mine"

            Dim objReport As New ReportDocument
            objReport.Load(Server.MapPath(Session("sqlUseReport").ToString) & ".rpt")
            objReport.SetDataSource(sDS)
            objReport.ParameterFields("filter").CurrentValues.AddValue(filterSTR)
      'objReport.ParameterFields("filter").CurrentValues.AddValue("filter")

      'objReport.PrintOptions.PaperOrientation = PaperOrientation.Landscape


      Dim expReport As ExportOptions
      expReport = objReport.ExportOptions
      expReport.FormatOptions = New PdfRtfWordFormatOptions 'PDF format option
      expReport.ExportFormatType = ExportFormatType.PortableDocFormat 'Set the format type of document
      Dim req As ExportRequestContext = New ExportRequestContext
      req.ExportInfo = expReport

      'Sets the report to HTML stream
      Dim st As System.IO.Stream
      st = objReport.FormatEngine.ExportToStream(req)

      objReport = Nothing 'Dispose the report

      'Stream content
      Response.ClearContent()
      Response.ClearHeaders()
      Response.ContentType = "application/pdf"

      Dim b(st.Length) As Byte
      st.Read(b, 0, st.Length) 'Read Stream
      Response.BinaryWrite(b) 'Write Stream
      st.Close() 'Close Stream
      st = Nothing 'Dispose

      Response.End()

    Catch ex As Exception

    Finally
      sConn.Close()
    End Try

  End Sub
End Class